﻿using System;
using System.Diagnostics;
using System.IO;
using System.Runtime.InteropServices;
using System.Security.Policy;
using System.Threading.Tasks;
using System.Windows.Controls;
using Excel;
using Microsoft.Win32;
using Stylet;
using StyletIoC;
using SharpCompress.Writers.Zip;
using SharpCompress.Archives.Zip;
using SharpCompress.Archives;

namespace WpfSeasonUpload.Pages
{
    public class ShellViewModel : Conductor<Screen>
    {
        private readonly IContainer _container;
        private readonly IWindowManager _windowManager;
        public ShellViewModel(IWindowManager windowManager, IContainer container)
        {
            this._windowManager = windowManager;
            this._container = container;

        }
        public string Season { get; set; } = "20203";
        public string Result { get; set; }
        public ApplicationClass App1 { get; set; }
        public ApplicationClass App2 { get; set; }
        public ApplicationClass App3 { get; set; }
        public ApplicationClass App4 { get; set; }
        public int Columns { get; set; }
        public int Rows { get; set; }
        public int App2Columns { get; set; }
        public int App2Rows { get; set; }
        public string Message { get; set; }
        public Worksheet worksheet { get; set; }
        public Worksheet worksheet2 { get; set; }
        public string Time { get; set; } = "共用时0秒";
        public Workbook workbook { get; set; }
        public Workbook workbook2 { get; set; }
        public Page1ViewModel Page1Model { get; private set; }
        public void OpenExcel1()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            openFileDialog.Title = "请选择消费表";
            openFileDialog.ShowDialog();
            var name = openFileDialog.FileName;
            if (string.IsNullOrEmpty(name))
            {
                this._windowManager.ShowMessageBox("请选择一个excel文件");
                return;
            }
            openFileDialog.Title = "请选择信息表";
            openFileDialog.ShowDialog();
            var name2 = openFileDialog.FileName;
            if (string.IsNullOrEmpty(name) || name2 == name)
            {
                this._windowManager.ShowMessageBox("请选择一个excel文件");
                return;
            }
            App1 = new ApplicationClass();
            App2 = new ApplicationClass();
            App2.Visible = true;
            App1.Visible = true;
            this.workbook = App1.Workbooks.Open(name);
            this.workbook2 = App2.Workbooks.Open(name2);
            this.worksheet = (Worksheet)workbook.Worksheets[1];
            this.worksheet2 = (Worksheet)workbook2.Worksheets[1];

        }

        private async void FillToExcel(Worksheet worksheet, Worksheet worksheet2)
        {
            this.Columns = GetMaxColumn(worksheet);
            this.Rows = GetMaxRow(worksheet);
            this.App2Columns = GetMaxColumn(worksheet2);
            this.App2Rows = GetMaxRow(worksheet2);
            await Task.Run(() =>
            {
                Stopwatch sp = new Stopwatch();
                sp.Start();
                for (int i = 1; i < Rows; i++)
                {
                    for (int j = 1; j < App2Rows; j++)
                    {

                        var excel1Name = GetCellValue(worksheet, $"f{i}");
                        if (string.IsNullOrEmpty(excel1Name)) break;
                        var excel2Name = GetCellValue(worksheet2, $"b{j}");

                        if (excel1Name == excel2Name)
                        {
                            Console.WriteLine(excel1Name + "找到相同");
                            var excel1入院日期 = GetCellValue(worksheet, $"i{i}").Replace("/", "-");
                            var excel2入院日期 = GetCellValue(worksheet2, $"m{j}");
                            if (excel1入院日期 == excel2入院日期)
                            {
                                this.Message = excel1Name + "---找到真正相等的人";
                                var excel2住院号 = GetCellValue(worksheet2, $"a{j}");
                                var excel2性别 = GetCellValue(worksheet2, $"d{j}");
                                var excel2年龄 = GetCellValue(worksheet2, $"e{j}");
                                var excel2电话 = GetCellValue(worksheet2, $"u{j}");
                                var excel2主要诊断 = GetCellValue(worksheet2, $"k{j}");
                                var excel2身份证 = GetCellValue(worksheet2, $"v{j}");
                                SetCellValue(worksheet, $"b{i}", excel2年龄);
                                SetCellValue(worksheet, $"h{i}", excel2住院号);
                                SetCellValue(worksheet, $"d{i}", excel2电话);
                                SetCellValue(worksheet, $"e{i}", excel2主要诊断);
                                SetCellValue(worksheet, $"c{i}", excel2身份证);
                                if (excel2性别 == "男")
                                {
                                    SetCellValue(worksheet, $"g{i}", "1");
                                }
                                else
                                {
                                    SetCellValue(worksheet, $"g{i}", "2");
                                }
                                break;
                            }
                        }


                    }
                }
                this.workbook.SaveAs(Path.GetFullPath("./消费表.xlsx"));
                sp.Stop();
                this.Time = $"共用时{sp.Elapsed.TotalSeconds}秒";
                Process.Start(Environment.CurrentDirectory);
                this.App1.Quit();
                this.App2.Quit();
                OffcieComHelper.Helper.Kill(this.App1);
                OffcieComHelper.Helper.Kill(this.App2);
            });
        }

        private string GetCellValue(Worksheet worksheet, string cell)
        {
            try
            {
                if (worksheet.Range[cell].Value2 != null)
                {
                    return worksheet.Range[cell].Value2.ToString();
                }
                return null;

            }
            catch (Exception ex)
            {

                return null;
            }

        }
        private void SetCellValue(Worksheet worksheet, string cell, string value)
        {
            try
            {
                worksheet.Range[cell].Value2 = value;
            }
            catch (Exception)
            {


            }

        }
        private int GetMaxRow(Worksheet worksheet)
        {
            return worksheet.UsedRange.Rows.Count;
        }

        private int GetMaxColumn(Worksheet worksheet)
        {
            return worksheet.UsedRange.Columns.Count;
        }
        public void AppClosed()
        {
            if (App1 != null)
            {
                App1.Quit();
                OffcieComHelper.Helper.Kill(this.App1);
            }
            if (App2 != null)
            {
                App2.Quit();
                OffcieComHelper.Helper.Kill(this.App2);
            }
            if (App3 !=null)
            {
                App3.Quit();
                OffcieComHelper.Helper.Kill(this.App3);
            }
            if (App4 != null)
            {
                App4.Quit();
                OffcieComHelper.Helper.Kill(this.App4);
            }
            FreeConsole();

        }
        public void Nav(object c)
        {
            var page1 = _container.Get<Page1ViewModel>();
            ActivateItem(page1);
        }
        public void Nav1(object c)
        {

        }
        public bool CanMergeTable
        {
            get { return App1 != null && App2 != null; }
        }
        public void MergeTable()
        {
            FillToExcel(this.worksheet, this.worksheet2);

        }
        /// <summary>
        /// Allocates a new console for current process.
        /// </summary>
        [DllImport("kernel32.dll")]
        public static extern Boolean AllocConsole();

        /// <summary>
        /// Frees the console.
        /// </summary>
        [DllImport("kernel32.dll")]
        public static extern Boolean FreeConsole();
        public void MergeToUploadTable()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Title = "请打开整合后的消费表";
            openFileDialog.ShowDialog();
            string name1 = openFileDialog.FileName;
            if (string.IsNullOrEmpty(name1))
            {
                this._windowManager.ShowMessageBox("未选择文件，请重新选择");
                return;
            }
            OpenFileDialog openFileDialog2 = new OpenFileDialog();
            openFileDialog2.Title = "请打开上传表";
            openFileDialog2.ShowDialog();
            string name2 = openFileDialog2.FileName;
            if (string.IsNullOrEmpty(name2))
            {
                this._windowManager.ShowMessageBox("未选择文件，请重新选择");
                return;
            }
            App3 = new ApplicationClass();
            
            App4 = new ApplicationClass();
           
            App3.Visible = true;
            App4.Visible = true;
            this.workbook = App3.Workbooks.Open(name1);
            this.workbook2 = App4.Workbooks.Open(name2);
            this.worksheet = (Worksheet)workbook.Worksheets[1];
            this.worksheet2 = (Worksheet)workbook2.Worksheets[1];
            var rows= GetMaxRow(this.worksheet);
            for (int i = 6; i < rows-2; i++)
            {
                int startrow = i-4;
                SetCellValue(worksheet2, $"a{startrow}", Season);
                SetCellValue(worksheet2, $"b{startrow}", "410000000204");
                SetCellValue(worksheet2, $"c{startrow}", "410000008102");
                SetCellValue(worksheet2, $"e{startrow}", GetCellValue(worksheet,$"b{i}"));
                SetCellValue(worksheet2, $"f{startrow}", "1");
                SetCellValue(worksheet2, $"g{startrow}", "01");
                SetCellValue(worksheet2, $"i{startrow}", GetCellValue(worksheet, $"f{i}"));
                SetCellValue(worksheet2, $"j{startrow}", GetCellValue(worksheet, $"g{i}"));
                SetCellValue(worksheet2, $"k{startrow}", GetCellValue(worksheet, $"ag{i}"));
                SetCellValue(worksheet2, $"l{startrow}","01");
                SetCellValue(worksheet2, $"m{startrow}", GetCellValue(worksheet, $"c{i}"));
                SetCellValue(worksheet2, $"n{startrow}", "20");
                SetCellValue(worksheet2, $"o{startrow}", "70");
                SetCellValue(worksheet2, $"p{startrow}", "410184");
                SetCellValue(worksheet2, $"q{startrow}", GetCellValue(worksheet, $"d{i}"));
                SetCellValue(worksheet2, $"r{startrow}", "451100");
                SetCellValue(worksheet2, $"s{startrow}","410184");
                SetCellValue(worksheet2, $"t{startrow}", "451100");
                SetCellValue(worksheet2, $"u{startrow}","2");
                SetCellValue(worksheet2, $"w{startrow}","02");
                SetCellValue(worksheet2, $"y{startrow}","02");
                SetCellValue(worksheet2, $"ae{startrow}","1");
                SetCellValue(worksheet2, $"ag{startrow}","1");
                SetCellValue(worksheet2, $"ar{startrow}", "1");
                SetCellValue(worksheet2, $"bo{startrow}", "410000008102");
                SetCellValue(worksheet2, $"v{startrow}", GetCellValue(worksheet, $"i{i}"));
                SetCellValue(worksheet2, $"x{startrow}", GetCellValue(worksheet, $"j{i}"));
                SetCellValue(worksheet2, $"z{startrow}", GetCellValue(worksheet, $"k{i}"));
                SetCellValue(worksheet2, $"aa{startrow}", GetCellValue(worksheet, $"e{i}"));
                SetCellValue(worksheet2, $"ad{startrow}", GetCellValue(worksheet, $"e{i}"));
                SetCellValue(worksheet2, $"as{startrow}", GetCellValue(worksheet, $"ac{i}"));
                SetCellValue(worksheet2, $"au{startrow}", GetCellValue(worksheet, $"ad{i}"));
                SetCellValue(worksheet2, $"az{startrow}", GetCellValue(worksheet, $"ae{i}"));
                SetCellValue(worksheet2, $"bi{startrow}", GetCellValue(worksheet, $"af{i}"));
                SetCellValue(worksheet2, $"bk{startrow}", GetCellValue(worksheet, $"aa{i}"));
                SetCellValue(worksheet2, $"bj{startrow}", GetCellValue(worksheet, $"ab{i}"));
                SetCellValue(worksheet2, $"bg{startrow}", GetCellValue(worksheet, $"x{i}"));
                SetCellValue(worksheet2, $"bf{startrow}", GetCellValue(worksheet, $"x{i}"));
                SetCellValue(worksheet2, $"ba{startrow}", GetCellValue(worksheet, $"z{i}"));
                SetCellValue(worksheet2, $"ax{startrow}", GetCellValue(worksheet, $"s{i}"));
                SetCellValue(worksheet2, $"ay{startrow}", GetCellValue(worksheet, $"w{i}"));
                SetCellValue(worksheet2, $"bn{startrow}", GetCellValue(worksheet, $"w{i}"));
            }
            workbook2.SaveAs(Path.GetFullPath("./XXB.xlsx"));
            //ZipWriter zip = new ZipWriter(new FileStream( "./XXB.zip",FileMode.Create),new ZipWriterOptions(SharpCompress.Common.CompressionType.Deflate) { });
            //zip.Write("", new FileStream("./XXB.xlsx",FileMode.Open),DateTime.Now);
            using (var archive = ZipArchive.Create())
            {
                archive.AddEntry("","./XXB.xlsx");
                archive.SaveTo(File.Open("./1.zip",FileMode.OpenOrCreate));
            }

        }
    }
}
